SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ===============================================
-- Author:		Wagner Silveira
-- Create date: 26/06/2006
-- Description:	Triggers an Notification Event for 
-- Updated and Inserted MessageTypes
-- ===============================================
CREATE TRIGGER dbo.trgServiceProviderSetNotification 
   ON  dbo.ServiceProvider 
   AFTER INSERT, UPDATE
AS 
BEGIN

	set nocount on;

	declare @serviceprovider varchar(10)
	declare @notifiedServiceProvider varchar(10)
	declare @BatchID bigint;

	select @serviceprovider = isnull(sp.serviceproviderid, '')
	from serviceprovider sp inner join inserted i
	on sp.serviceproviderid = i.serviceproviderid
	where sp.publicKey <> i.publicKey

	if (@serviceprovider <> '')
	begin

		-- Open Event Batch
		exec CN.NSEventBeginBatchServiceProviderChangesData N'ServiceProviderSPEventProvider', @BatchID OUTPUT;

		-- Defines a cursor to get the list of Providers affected by the change.
		declare ServiceProviderList cursor local for
			select distinct Origin.ServiceProviderID
			from
			(select p.PoolId, p.PoolName,
				sp.ServiceProviderId,
				spt.ServiceProviderTypeID
			from Pool p
				inner join ServiceProviderPool spp on spp.PoolID = p.PoolID
				inner join ServiceProvider sp on spp.ServiceProviderID = sp.ServiceProviderID
				inner join ServiceProviderType  spt on spt.ServiceProviderTypeID = sp.ServiceProviderTypeID) as Origin
			inner join
			(select p.PoolID,
				sp.ServiceProviderID,
				sp.ServiceProviderTypeID
			from Pool p
				inner join ServiceProviderPool spp on spp.PoolID = p.PoolID
				inner join ServiceProvider sp on spp.ServiceProviderID = sp.ServiceProviderID) as Destination
				on Origin.PoolID = Destination.PoolID
				inner join ServiceProviderTypeMessageType SPTMT on SPTMT.sourceServiceProviderTypeID = Origin.ServiceProviderTypeID
					and SPTMT.DestinationServiceProviderTypeID = Destination.ServiceProviderTypeID
			where Destination.ServiceProviderID = @serviceprovider

		open ServiceProviderList

		fetch next from ServiceProviderList into @notifiedServiceProvider

		while @@fetch_status = 0
		begin
			
			-- Write a new event within the Batch
			exec CN.NSEventWriteServiceProviderChangesData @BatchID, @notifiedServiceProvider
			
			fetch next from ServiceProviderList into @notifiedServiceProvider
		end


		close ServiceProviderList
		deallocate ServiceProviderList

		-- Close the Event Batch
		exec CN.NSEventFlushBatchServiceProviderChangesData @BatchID
		
	end

END
GO
